# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：get_stock_rank.py
#日期：2019-04-29
#备注：获取东方财富网的每天统计的股吧关注人数排行到数据库，rankInfo_to_mysql()  
       http://guba.eastmoney.com/list,600519.html   
select rank_date as 统计日期,stock_No as 股票代码,stock_name as 股票名称,rank_type as 统计类型,rank_num as 活跃排名,price_new as 收盘价,price_chg as 涨跌幅,
turnover as 交易量亿,turnover_rate as 换手率,create_time as 统计时间,core_detail as 核心题材 from rank_top50 where rank_date='20200618' order by rank_type,rank_num;

select rank_date as 统计日期,stock_No as 股票代码,stock_name as 股票名称,rank_type as 统计类型,rank_num as 活跃排名,price_new as 收盘价,price_chg as 涨跌幅,
turnover as 交易量亿,turnover_rate as 换手率,create_time as 统计时间,core_detail as 核心题材 from rank_top50 where rank_date='20200618' order by rank_type desc,rank_num;
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import requests
import time
from pacong.stock.stock_config import db,cursor
from pycacho.cachobase.chinese_workday import get_trade_day
from pycacho.cachobase.logger import Logger

logger = Logger("get_stock_rank").get_log()

trade_date = get_trade_day(0,'s')   # 20200519
table = 'stock_tradeInfo_' + trade_date

headers = {
    "Referer": "http://guba.eastmoney.com",
    "User-Agent": "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.129 Safari/537.36",
    "Content-Type": "application/x-www-form-urlencoded"
}

def createTable():
    sql1 = 'drop table if exists stock_rankInfo;'
    sql2 = 'create table stock_rankInfo(trade_date varchar(20),rank_stockNo varchar(20),rank_num int(10),create_time datetime,PRIMARY KEY (rank_stockNo));'

    cursor.execute(sql1)
    cursor.execute(sql2)
    db.commit()


def get_stockNo():
    sql = 'select stock_no from china_stock;'
    cursor.execute(sql)
    results = cursor.fetchall()
    stocks = []
    for stock in results:
        stock_no = stock[0]
        stocks.append(stock_no)
    return stocks


# http://guba.eastmoney.com/list,605168.html
def get_info():
    param = {
        "param":'code=000009',
        "path":"webarticlelist/api/guba/gubainfo",
        "env": "2"
    }
    url = 'http://guba.eastmoney.com/interface/GetData.aspx'
    stocks = get_stockNo()
    for i in range(len(stocks)):
        n = stocks.index('000001')         # 000001
        if i >= n:
            code = 'code=' + stocks[i]
            param['param'] = code
            try:
                req1 = requests.post(url, data=param,headers=headers)
                rank_num = eval(req1.text)['popular_rank']
                #print(rank_date,stocks[i],rank_num)
                rankInfo_to_mysql(trade_date,stocks[i],rank_num)
            except:
                time.sleep(1000000)


def rankInfo_to_mysql(trade_date,rank_stockNo,rank_num):
    sql = "insert into stock_rankInfo (trade_date,rank_stockNo,rank_num,create_time) values ('%s','%s','%s',now());" % (trade_date,rank_stockNo,rank_num)
    try:
        cursor.execute(sql)
    except Exception as e:
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('东方财富股吧股票-->%s人气活跃度排名信息插入成功！',rank_stockNo)


def merge():
    sql1 = 'delete from rank_top50 where trade_date="%s"' % (trade_date)
    sql2 = 'insert into rank_top50 select r.trade_date as 统计日期,r.rank_stockNo as 股票代码,t.stock_name as 股票名称,"涨停",r.rank_num as 活跃排名,t.price_new as 收盘价,t.price_chg as 涨跌幅,t.turnover/100000000 as 交易量亿,t.turnover_rate as 换手率,c.core_detail as 核心题材,r.create_time ' \
           'from stock_rankinfo r,%s t,stock_coreinfo c ' \
           'where r.rank_stockNo=t.stock_no and t.stock_no=c.core_stNo and t.price_new>2  and r.rank_num<51 and t.price_chg>19.8 and core_theme="所属板块" order by r.rank_num;' %(table)
    sql3 = 'insert into rank_top50 select r.trade_date as 统计日期,r.rank_stockNo as 股票代码,t.stock_name as 股票名称,"非涨停",r.rank_num as 活跃排名,t.price_new as 收盘价,t.price_chg as 涨跌幅,t.turnover/100000000 as 交易量亿,t.turnover_rate as 换手率,c.core_detail as 核心题材,r.create_time ' \
           'from stock_rankinfo r,%s t,stock_coreinfo c ' \
           'where r.rank_stockNo=t.stock_no and t.stock_no=c.core_stNo and t.price_new>2 and r.rank_num<51 and t.price_chg<19.8 and core_theme="所属板块" order by r.rank_num;' % (table)
    cursor.execute(sql1)
    cursor.execute(sql2)
    cursor.execute(sql3)
    db.commit()
    logger.info("东方财富股吧活跃度前50名涨停股票信息存入到rank_top50表成功！")


if __name__ == '__main__':
    createTable()
    get_info()
    merge()



